﻿Imports AugmentLib.Database
Imports System.Configuration.ConfigurationManager
Partial Class unit
    Inherits System.Web.UI.Page

#Region " Properties "

    Public Property CurrentPage() As Integer
        Get
            'Looks for the current page in ViewState
            Dim objPage As Object = ViewState("_CurrentPage")
            If objPage Is Nothing Then
                Return 0
            Else
                'default to showing the first page
                Return CInt(objPage)
            End If
        End Get
        Set(ByVal value As Integer)
            ViewState("_CurrentPage") = value
        End Set
    End Property

    Public Property PageSize() As Integer
        Get
            'Looks for the current page in ViewState
            Dim objPageSize As Object = ViewState("_PageSize")
            If objPageSize Is Nothing Then
                Return 25
            Else
                'default to showing the first page
                Return CInt(objPageSize)
            End If
        End Get
        Set(ByVal value As Integer)
            ViewState("_PageSize") = value
        End Set
    End Property

#End Region

#Region " Paging and Page Size "

    Protected Sub lnkHead10Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead10Item.Click
        PageSize = 10
        LoadUnitNumber()
    End Sub

    Protected Sub lnkHead25Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead25Item.Click
        PageSize = 25
        LoadUnitNumber()
    End Sub

    Protected Sub lnkHead100Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead100Item.Click
        PageSize = 100
      LoadUnitNumber()
    End Sub

    Protected Sub lnkHead150Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead150Item.Click
        PageSize = 150
       LoadUnitNumber()
    End Sub

    Protected Sub lnkFoot10Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot10Item.Click
        PageSize = 10
     LoadUnitNumber()
    End Sub

    Protected Sub lnkFoot25Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot25Item.Click
        PageSize = 25
       LoadUnitNumber()
    End Sub

    Protected Sub lnkFoot100Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot100Item.Click
        PageSize = 100
       LoadUnitNumber()
    End Sub

    Protected Sub lnkFoot150Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot150Item.Click
        PageSize = 150
        LoadUnitNumber()
    End Sub

    Protected Sub btnMoveFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
        CurrentPage = 0
        txtPageNo.Text = CurrentPage + 1
       LoadUnitNumber()
    End Sub

    Protected Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If CurrentPage > 0 Then
            CurrentPage = CurrentPage - 1
            txtPageNo.Text = CurrentPage + 1
           LoadUnitNumber()
        End If
    End Sub

    Protected Sub btnGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGo.Click
        If CInt(txtPageNo.Text < 1) Then
            txtPageNo.Text = 1
        End If

        If CInt(txtPageNo.Text > CInt(lblMaxPageCount.Text)) Then
            txtPageNo.Text = CInt(lblMaxPageCount.Text) + 1
        End If

        CurrentPage = CInt(txtPageNo.Text) - 1
       LoadUnitNumber()

    End Sub

    Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If CurrentPage < CInt(lblMaxPageCount.Text) Then
            CurrentPage = CurrentPage + 1
            txtPageNo.Text = CurrentPage + 1
         LoadUnitNumber()
        End If
    End Sub

    Protected Sub btnMoveLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
        CurrentPage = CInt(lblMaxPageCount.Text)
        txtPageNo.Text = CurrentPage + 1
        LoadUnitNumber()
    End Sub

#End Region

    Private Sub LoadUnitNumber()

        Dim CountryFilter As String = String.Empty
        Dim StateFilter As String = String.Empty
        Dim LocationFilter As String = String.Empty
        Dim RoadFilter As String = String.Empty
        Dim ProjectFilter As String = String.Empty
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        Dim TempDT As New Data.DataTable

        objDB.OpenConnection()

        'to add in filtering for country, state
        'edited by minginn @ 28/6/2011 - to load the listing according to search criteria after edit but not after save
        'If strCountry <> "" Then
        '    CountryFilter = " AND s.CountryId = '" & strCountry & "'"
        'End If

        'If strState <> "" Then
        '    StateFilter = " AND s.StateId = '" & strState & "'"
        'End If

        'If strLocation <> "" Then
        '    LocationFilter = " AND l.LocationId = '" & strLocation & "'"
        'End If

        'If strRoad <> "" Then
        '    RoadFilter = " AND r.RoadId = '" & strRoad & "'"
        'End If

        'If strProject <> "" Then
        '    ProjectFilter = " AND p.ProjectId = '" & strProject & "'"
        'End If

        If ddlCountryFilter.SelectedValue <> "" Then
            CountryFilter = " AND s.CountryId = '" & ddlCountry.SelectedValue & "'"
        End If

        If ddlStateFilter.SelectedValue <> "" Then
            StateFilter = " AND s.StateId = '" & ddlStateFilter.SelectedValue & "'"
        End If

        If ddlLocationFilter.SelectedValue <> "" Then
            LocationFilter = " AND l.LocationId = '" & ddlLocationFilter.SelectedValue & "'"
        End If

        If ddlRoadFilter.SelectedValue <> "" Then
            RoadFilter = " AND r.RoadId = '" & ddlRoadFilter.SelectedValue & "'"
        End If

        If ddlProjectFilter.SelectedValue <> "" Then
            ProjectFilter = " AND p.ProjectId = '" & ddlProjectFilter.SelectedValue & "'"
        End If
        If txtSearch.Text = "" Then
            objDB.dbVariable.SQLString = "SELECT UnitId,UnitFormat,p.ProjectId,Project,r.RoadId,Road,l.LocationId,Location,l.StateId,s.CountryId,State,Country FROM AW_State s " & _
                                        "LEFT OUTER JOIN AW_Country c ON s.CountryId = c.CountryId " & _
                                        "LEFT OUTER JOIN AW_Location l ON l.StateId = s.StateId " & _
                                        "LEFT OUTER JOIN AW_Road r ON r.LocationId = l.LocationId " & _
                                        "LEFT OUTER JOIN AW_Unit u ON u.RoadId = r.RoadId " & _
                                        "LEFT OUTER JOIN AW_Project p ON p.ProjectId = u.ProjectId " & _
                                        "WHERE u.StatusDelete=0 " & CountryFilter & StateFilter & LocationFilter & RoadFilter & ProjectFilter & LocationFilter & lblHidSortType.Text
            objDB.FillData(TempDT, "AW_UnitNumber", Database.SQLCommandType.NormalString)
        Else
            objDB.dbVariable.SQLString = "SELECT UnitId,UnitFormat,p.ProjectId,Project,r.RoadId,Road,l.LocationId,Location,l.StateId,s.CountryId,State,Country FROM AW_State s " & _
                                        "LEFT OUTER JOIN AW_Country c ON s.CountryId = c.CountryId " & _
                                        "LEFT OUTER JOIN AW_Location l ON l.StateId = s.StateId " & _
                                        "LEFT OUTER JOIN AW_Road r ON r.LocationId = l.LocationId " & _
                                        "LEFT OUTER JOIN AW_Unit u ON u.RoadId = r.RoadId " & _
                                        "LEFT OUTER JOIN AW_Project p ON p.ProjectId = u.ProjectId " & _
                                        "WHERE u.StatusDelete=0 AND UnitFormat LIKE @UnitFormat" & CountryFilter & StateFilter & LocationFilter & RoadFilter & ProjectFilter & lblHidSortType.Text
            objDB.AddParameter("UnitFormat", Data.SqlDbType.NVarChar, "%" & txtSearch.Text.Trim & "%")
            objDB.FillData(TempDT, "AW_UnitNumber", Database.SQLCommandType.NormalString)
        End If
        objDB.CloseConnection()

        'Creating a Paged Datasouce
        Dim objPaged As New PagedDataSource
        objPaged.DataSource = TempDT.DefaultView
        objPaged.AllowPaging = True
        objPaged.PageSize = PageSize
        objPaged.CurrentPageIndex = CurrentPage

        lblHeadNoOfItem.Text = TempDT.Rows.Count
        lblFootNoOfItem.Text = TempDT.Rows.Count
        lblMaxPageCount.Text = objPaged.PageCount - 1

        repUnitNumber.DataSource = objPaged
        repUnitNumber.DataBind()

    End Sub

    Protected Sub imgNewUnitNumber_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgNewUnitNumber.Click
        GetddlCountry()
        GetddlState(ddlCountry.SelectedValue)
        GetddlLocation(ddlState.SelectedValue)
        GetddlRoad(ddlLocation.SelectedValue)
        GetddlProject(ddlRoad.SelectedValue)
        txtUnitNumber.Text = String.Empty
        ClearAllField()
        mpeEntry.Show()
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Session("StaffId") Is Nothing Then
            Response.Redirect("Default.aspx")
        ElseIf Session.Item("StaffType").ToString = "2" Then
            Response.Redirect("Default.aspx")
        Else
            lblStaffUID.Text = Session("StaffId").ToString
        End If

        If Not Page.IsPostBack Then
            GetddlCountryFilter()
            GetddlStateFilter(ddlCountryFilter.SelectedValue)
            GetddlLocationFilter(ddlStateFilter.SelectedValue)
            GetddlRoadFilter(ddlLocationFilter.SelectedValue)
            GetddlProjectFilter(ddlRoadFilter.SelectedValue)
            LoadUnitNumber()
            'Added by : Daniel Lee
            'Date : 22-Apr-2011
            'Purpose : To auto update each other when typing the search criteria
            txtSearch.Attributes.Add("onKeyUp", "SearchReplace('" & txtSearch.ClientID & "','" & txtFilterSearch.ClientID & "')")
            txtSearch.Attributes.Add("onPaste", "SearchReplace('" & txtSearch.ClientID & "','" & txtFilterSearch.ClientID & "')")
            txtFilterSearch.Attributes.Add("onKeyUp", "SearchReplace('" & txtFilterSearch.ClientID & "','" & txtSearch.ClientID & "')")
            txtFilterSearch.Attributes.Add("onPaste", "SearchReplace('" & txtFilterSearch.ClientID & "','" & txtSearch.ClientID & "')")

        End If
    End Sub

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        ddlCountryFilter.SelectedIndex = 0
        ddlStateFilter.SelectedIndex = 0
        ddlLocationFilter.SelectedIndex = 0
        ddlRoadFilter.SelectedIndex = 0
        txtFilterSearch.Text = txtSearch.Text.Trim
        LoadUnitNumber()
    End Sub

    Protected Sub repUnitNumber_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.RepeaterCommandEventArgs) Handles repUnitNumber.ItemCommand
        If e.CommandName.ToUpper = "EDITITEM" Then
            GetddlCountry()
            ddlCountry.SelectedValue = CType(repUnitNumber.Items(e.CommandArgument).FindControl("lblRepCountryUID"), Label).Text
            GetddlState(ddlCountry.SelectedValue)
            ddlState.SelectedValue = CType(repUnitNumber.Items(e.CommandArgument).FindControl("lblRepStateUID"), Label).Text
            GetddlLocation(ddlState.SelectedValue)
            ddlLocation.SelectedValue = CType(repUnitNumber.Items(e.CommandArgument).FindControl("lblRepLocationUID"), Label).Text
            GetddlRoad(ddlLocation.SelectedValue)
            ddlRoad.SelectedValue = CType(repUnitNumber.Items(e.CommandArgument).FindControl("lblRepRoadUID"), Label).Text
            GetddlProject(ddlRoad.SelectedValue)
            ddlProject.SelectedValue = CType(repUnitNumber.Items(e.CommandArgument).FindControl("lblRepProjectUID"), Label).Text
            txtUnitNumber.Text = CType(repUnitNumber.Items(e.CommandArgument).FindControl("lblRepUnitFormat"), Label).Text
            lblUnitNumberUID.Text = CType(repUnitNumber.Items(e.CommandArgument).FindControl("lblRepUnitNumberUID"), Label).Text
            mpeEntry.Show()
        Else
            'Begin Delete
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

            objDB.OpenConnection()
            objDB.dbVariable.SQLString = "UPDATE AW_Unit SET StatusDelete=1 WHERE UnitId=@UnitId"
            objDB.AddParameter("UnitId", Data.SqlDbType.UniqueIdentifier, New Guid(CType(repUnitNumber.Items(e.CommandArgument).FindControl("lblRepUnitNumberUID"), Label).Text))
            objDB.ExecuteQuery()
            objDB.CloseConnection()
            lblMsgSuccessful.Text = "Unit Number Format Deleted"
            lblMsgSuccessful.Visible = True
           LoadUnitNumber()
        End If
    End Sub

    Private Sub ClearAllField()
        txtUnitNumber.Text = String.Empty
        ddlRoad.SelectedIndex = 0
        ddlLocation.SelectedIndex = 0
        ddlCountry.SelectedIndex = 0
        ddlState.SelectedIndex = 0
        ddlProject.SelectedIndex = 0
        lblUnitNumberUID.Text = String.Empty
        divError.Visible = False
        divSuccessful.Visible = False
    End Sub

    Private Sub ClearFilterField()
        txtSearch.Text = String.Empty
        txtFilterSearch.Text = String.Empty
        ddlRoadFilter.SelectedIndex = 0
        ddlLocationFilter.SelectedIndex = 0
        ddlCountryFilter.SelectedIndex = 0
        ddlStateFilter.SelectedIndex = 0
        ddlProjectFilter.SelectedIndex = 0
        divError.Visible = False
        divSuccessful.Visible = False
    End Sub

    Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click

        If lblUnitNumberUID.Text = "" Then
            'New
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

            objDB.OpenConnection()
            If UnitNumberExist(objDB) Then
                ClearAllField()
                divError.Visible = True
                lblMsgError.Text = "Unit Number Format Already Exist"
                ClearFilterField()
                LoadUnitNumber()
            Else
                objDB.dbVariable.SQLString = "INSERT INTO AW_Unit (UnitId,CountryId,StateId,LocationId,RoadId,ProjectId,UnitFormat,CreateDate,StatusDelete) VALUES (@UnitId,@CountryId,@StateId,@LocationId,@RoadId,@ProjectId,@UnitFormat,@CreateDate,@StatusDelete)"
                objDB.AddParameter("UnitId", Data.SqlDbType.UniqueIdentifier, Guid.NewGuid)
                objDB.AddParameter("CountryId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlCountry.SelectedValue))
                objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlState.SelectedValue))
                objDB.AddParameter("LocationId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlLocation.SelectedValue))
                objDB.AddParameter("RoadId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlRoad.SelectedValue))
                If ddlProject.SelectedIndex = 0 Then
                    objDB.AddParameter("ProjectId", Data.SqlDbType.UniqueIdentifier, DBNull.Value)
                Else
                    objDB.AddParameter("ProjectId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlProject.SelectedValue))
                End If
                objDB.AddParameter("UnitFormat", Data.SqlDbType.NVarChar, txtUnitNumber.Text.Trim.ToUpper)
                objDB.AddParameter("CreateDate", Data.SqlDbType.DateTime, Now)
                objDB.AddParameter("StatusDelete", Data.SqlDbType.TinyInt, 0)
                objDB.ExecuteQuery()
                ClearAllField()
                ClearFilterField()
                divSuccessful.Visible = True
                lblMsgSuccessful.Text = "Unit Number Format Successfully Inserted"
                LoadUnitNumber()
            End If
            objDB.CloseConnection()
        Else
            'Edit
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

            objDB.OpenConnection()
            objDB.dbVariable.SQLString = "UPDATE AW_Unit SET UnitFormat=@UnitFormat WHERE UnitId=@UnitId"
            objDB.AddParameter("UnitId", Data.SqlDbType.UniqueIdentifier, New Guid(lblUnitNumberUID.Text))
            objDB.AddParameter("UnitFormat", Data.SqlDbType.NVarChar, txtUnitNumber.Text.Trim.ToUpper)
            objDB.ExecuteQuery()
            objDB.CloseConnection()
            ClearAllField()
            divSuccessful.Visible = True
            lblMsgSuccessful.Text = "Unit Number Format Successfully Updated"
            LoadUnitNumber()
        End If
    End Sub

    Private Function UnitNumberExist(ByRef objDB As Database) As Boolean
        Dim Tempdt As New Data.DataTable
        Dim projectFilter As String = String.Empty
        Dim DBUnitFormat As String
        Dim unitNumber As String
        Dim blnUnitFormat As Boolean = False

        unitNumber = txtUnitNumber.Text.Trim

        If ddlProject.SelectedValue <> "" Then
            projectFilter = " AND ProjectId=@ProjectId"
            objDB.AddParameter("ProjectId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlProject.SelectedValue))
        Else
            projectFilter = " AND ProjectId IS NULL"
        End If

        objDB.dbVariable.SQLString = "SELECT UnitFormat, LEN(UnitFormat) as UnitLength FROM AW_Unit WHERE CountryId=@CountryId AND StateId=@StateId " & _
        "AND LocationId=@LocationId AND RoadId=@RoadId" & projectFilter & " AND StatusDelete=0"
        objDB.AddParameter("CountryId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlCountry.SelectedValue))
        objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlState.SelectedValue))
        objDB.AddParameter("LocationId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlLocation.SelectedValue))
        objDB.AddParameter("RoadId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlRoad.SelectedValue))
        objDB.FillData(Tempdt, "AW_Unit", Database.SQLCommandType.NormalString)
        objDB.ClearParameter()

        If Tempdt.Rows.Count > 0 Then
            Tempdt.TableName = "tblUnitFormat"
            Dim tempView As New Data.DataView(Tempdt)
            tempView.RowFilter = "UnitLength = " & unitNumber.Length

            For n As Integer = 0 To tempView.Count - 1
                DBUnitFormat = tempView(n).Item("UnitFormat").ToString()
                For w As Integer = 0 To DBUnitFormat.Length - 1
                    If Char.IsLetter(DBUnitFormat.Substring(w, 1)) = True Then
                        If Char.IsLetter(unitNumber.Substring(w, 1)) = True Then
                            blnUnitFormat = True
                        Else
                            blnUnitFormat = False
                            Exit For
                        End If
                    ElseIf Char.IsNumber(DBUnitFormat.Substring(w, 1)) = True Then
                        If Char.IsNumber(unitNumber.Substring(w, 1)) = True Then
                            blnUnitFormat = True
                        Else
                            blnUnitFormat = False
                            Exit For
                        End If
                    Else
                        If unitNumber.Substring(w, 1) = DBUnitFormat.Substring(w, 1) Then
                            blnUnitFormat = True
                        Else
                            blnUnitFormat = False
                            Exit For
                        End If
                    End If
                Next   '--to determine the format of character
                If blnUnitFormat Then
                    Return blnUnitFormat
                End If
            Next   '--to loop around the length of the string
            Return blnUnitFormat
        Else
            ''    '--if no unit number format
            ''    '--Check not consist dashed, then true
            ''    If InStr(unitNumber, "-") > 0 Then
            blnUnitFormat = False
            ''    Else
            ''        blnUnitFormat = True
            ''End If
            Return blnUnitFormat
        End If
    End Function

    Private Sub GetddlCountry()
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT CountryId,Country FROM AW_Country WHERE StatusDelete = 0 ORDER BY Country"
        objDB.FillData(TempDT, "Country", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlCountry
            .DataSource = TempDT
            .DataTextField = "Country"
            .DataValueField = "CountryId"
            .DataBind()
            .Items.Insert(0, "-SELECT-")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()
    End Sub

    Private Sub GetddlState(ByVal strCountry As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strCountry = "" Then
            strCountry = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT StateId,State FROM AW_State WHERE CountryId = @CountryId AND StatusDelete = 0 ORDER BY State"
        objDB.AddParameter("CountryId", Data.SqlDbType.UniqueIdentifier, New Guid(strCountry))
        objDB.FillData(TempDT, "State", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlState
            .DataSource = TempDT
            .DataTextField = "State"
            .DataValueField = "StateId"
            .DataBind()
            .Items.Insert(0, "-SELECT-")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlLocation(ByVal strState As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strState = "" Then
            strState = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT LocationId,Location FROM AW_Location WHERE StateId = @StateId AND StatusDelete = 0 ORDER BY Location"
        objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(strState))
        objDB.FillData(TempDT, "Location", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlLocation
            .DataSource = TempDT
            .DataTextField = "Location"
            .DataValueField = "LocationId"
            .DataBind()
            .Items.Insert(0, "-SELECT-")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlRoad(ByVal strLocation As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strLocation = "" Then
            strLocation = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT RoadId,Road FROM AW_Road WHERE LocationId = @LocationId AND StatusDelete = 0 ORDER BY Road"
        objDB.AddParameter("LocationId", Data.SqlDbType.UniqueIdentifier, New Guid(strLocation))
        objDB.FillData(TempDT, "Road", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlRoad
            .DataSource = TempDT
            .DataTextField = "Road"
            .DataValueField = "RoadId"
            .DataBind()
            .Items.Insert(0, "-SELECT-")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlProject(ByVal strRoad As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strRoad = "" Then
            strRoad = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT ProjectId,Project FROM AW_Project WHERE RoadId = @RoadId AND StatusDelete = 0 ORDER BY Project"
        objDB.AddParameter("RoadId", Data.SqlDbType.UniqueIdentifier, New Guid(strRoad))
        objDB.FillData(TempDT, "Project", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlProject
            .DataSource = TempDT
            .DataTextField = "Project"
            .DataValueField = "ProjectId"
            .DataBind()
            .Items.Insert(0, "-SELECT-")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlCountryFilter()
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT CountryId,Country FROM AW_Country WHERE StatusDelete = 0 ORDER BY Country"
        objDB.FillData(TempDT, "Country", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "ANY"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlCountryFilter
            .DataSource = TempDT
            .DataTextField = "Country"
            .DataValueField = "CountryId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub


    Private Sub GetddlStateFilter(ByVal strCountry As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strCountry = "" Then
            strCountry = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT StateId,State FROM AW_State WHERE CountryId = @CountryId AND StatusDelete = 0 ORDER BY State"
        objDB.AddParameter("CountryId", Data.SqlDbType.UniqueIdentifier, New Guid(strCountry))
        objDB.FillData(TempDT, "State", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "ANY"
        'TempDT.Rows.InsertAt(dtrow, 0)      

        With ddlStateFilter
            .DataSource = TempDT
            .DataTextField = "State"
            .DataValueField = "StateId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlLocationFilter(ByVal strState As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strState = "" Then
            strState = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT LocationId,Location FROM AW_Location WHERE StateId = @StateId AND StatusDelete = 0 ORDER BY Location"
        objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(strState))
        objDB.FillData(TempDT, "Location", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlLocationFilter
            .DataSource = TempDT
            .DataTextField = "Location"
            .DataValueField = "LocationId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlRoadFilter(ByVal strLocation As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strLocation = "" Then
            strLocation = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT RoadId,Road FROM AW_Road WHERE LocationId = @LocationId AND StatusDelete = 0 ORDER BY Road"
        objDB.AddParameter("LocationId", Data.SqlDbType.UniqueIdentifier, New Guid(strLocation))
        objDB.FillData(TempDT, "Road", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlRoadFilter
            .DataSource = TempDT
            .DataTextField = "Road"
            .DataValueField = "RoadId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlProjectFilter(ByVal strRoad As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strRoad = "" Then
            strRoad = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT ProjectId,Project FROM AW_Project WHERE RoadId = @RoadId AND StatusDelete = 0 ORDER BY Project"
        objDB.AddParameter("RoadId", Data.SqlDbType.UniqueIdentifier, New Guid(strRoad))
        objDB.FillData(TempDT, "Project", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlProjectFilter
            .DataSource = TempDT
            .DataTextField = "Project"
            .DataValueField = "ProjectId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Protected Sub btnSearchFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearchFilter.Click
        txtSearch.Text = txtFilterSearch.Text.Trim
        LoadUnitNumber()
    End Sub

    Protected Sub btnResetSearchFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnResetSearchFilter.Click
        txtFilterSearch.Text = String.Empty
        txtSearch.Text = String.Empty
        ddlCountryFilter.SelectedIndex = 0
        ddlStateFilter.SelectedIndex = 0
        ddlLocationFilter.SelectedIndex = 0
        ddlRoadFilter.SelectedIndex = 0
        ddlProjectFilter.SelectedIndex = 0
    End Sub

    Protected Sub ddlCountryFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCountryFilter.SelectedIndexChanged
        GetddlStateFilter(ddlCountryFilter.SelectedValue)
        LoadUnitNumber()
    End Sub

    Protected Sub ddlStateFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlStateFilter.SelectedIndexChanged
        GetddlLocationFilter(ddlStateFilter.SelectedValue)
        LoadUnitNumber()
    End Sub

    Protected Sub ddlLocationFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlLocationFilter.SelectedIndexChanged
        GetddlRoadFilter(ddlLocationFilter.SelectedValue)
        LoadUnitNumber()
    End Sub

    Protected Sub ddlRoadFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlRoadFilter.SelectedIndexChanged
        GetddlProjectFilter(ddlRoadFilter.SelectedValue)
        LoadUnitNumber()
    End Sub

    Protected Sub ddlProjectFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlProjectFilter.SelectedIndexChanged
        LoadUnitNumber()
    End Sub

    Protected Sub ddlCountry_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCountry.SelectedIndexChanged
        GetddlState(ddlCountry.SelectedValue)
        mpeEntry.Show()
    End Sub

    Protected Sub ddlState_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlState.SelectedIndexChanged
        GetddlLocation(ddlState.SelectedValue)
        mpeEntry.Show()
    End Sub

    Protected Sub ddlLocation_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlLocation.SelectedIndexChanged
        GetddlRoad(ddlLocation.SelectedValue)
        mpeEntry.Show()
    End Sub

    Protected Sub ddlRoad_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlRoad.SelectedIndexChanged
        GetddlProject(ddlRoad.SelectedValue)
        mpeEntry.Show()
    End Sub

    Private Function SortType(ByVal strSortType As String) As String
        Dim strSort As String
        If Right(strSortType, 1) = "▼" Then
            strSort = "ASC"
        Else
            strSort = "DESC"
        End If
        Return strSort
    End Function

    Protected Sub lnkUnitNumber_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkUnitNumber.Click
        If lnkUnitNumber.Text = "Unit Number▼" Then
            lnkUnitNumber.Text = "Unit Number▲"
        Else
            lnkUnitNumber.Text = "Unit Number▼"
        End If

        lblHidSortType.Text = " ORDER BY UnitFormat " & SortType(lnkUnitNumber.Text) & ", Project " & SortType(lnkProject.Text) & _
                            ", Road " & SortType(lnkRoad.Text) & ", Location " & SortType(lnkLocation.Text) & _
                            ", State " & SortType(lnkState.Text) & ", Country " & SortType(lnkCountry.Text)
        LoadUnitNumber()
    End Sub

    Protected Sub lnkProject_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkProject.Click
        If lnkProject.Text = "Project▼" Then
            lnkProject.Text = "Project▲"
        Else
            lnkProject.Text = "Project▼"
        End If

        lblHidSortType.Text = " ORDER BY Project " & SortType(lnkProject.Text) & ", UnitFormat " & SortType(lnkUnitNumber.Text) & _
                            ", Road " & SortType(lnkRoad.Text) & ", Location " & SortType(lnkLocation.Text) & _
                            ", State " & SortType(lnkState.Text) & ", Country " & SortType(lnkCountry.Text)
        LoadUnitNumber()
    End Sub

    Protected Sub lnkRoad_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkRoad.Click
        If lnkRoad.Text = "Road▼" Then
            lnkRoad.Text = "Road▲"
        Else
            lnkRoad.Text = "Road▼"
        End If

        lblHidSortType.Text = " ORDER BY Road " & SortType(lnkRoad.Text) & ", UnitFormat " & SortType(lnkUnitNumber.Text) & _
                            ", Project " & SortType(lnkProject.Text) & ", Location " & SortType(lnkLocation.Text) & _
                            ", State " & SortType(lnkState.Text) & ", Country " & SortType(lnkCountry.Text)
        LoadUnitNumber()
    End Sub

    Protected Sub lnkLocation_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkLocation.Click
        If lnkLocation.Text = "Location▼" Then
            lnkLocation.Text = "Location▲"
        Else
            lnkLocation.Text = "Location▼"
        End If

        lblHidSortType.Text = " ORDER BY Location " & SortType(lnkLocation.Text) & ", UnitFormat " & SortType(lnkUnitNumber.Text) & _
                            ", Project " & SortType(lnkProject.Text) & ", Road " & SortType(lnkRoad.Text) & _
                            ", State " & SortType(lnkState.Text) & ", Country " & SortType(lnkCountry.Text)
        LoadUnitNumber()
    End Sub

    Protected Sub lnkState_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkState.Click
        If lnkState.Text = "State▼" Then
            lnkState.Text = "State▲"
        Else
            lnkState.Text = "State▼"
        End If

        lblHidSortType.Text = " ORDER BY State " & SortType(lnkState.Text) & ", UnitFormat " & SortType(lnkUnitNumber.Text) & _
                            ", Project " & SortType(lnkProject.Text) & ", Road " & SortType(lnkRoad.Text) & _
                            ", Location " & SortType(lnkLocation.Text) & ", Country " & SortType(lnkCountry.Text)
        LoadUnitNumber()
    End Sub

    Protected Sub lnkCountry_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkCountry.Click
        If lnkCountry.Text = "Country▼" Then
            lnkCountry.Text = "Country▲"
        Else
            lnkCountry.Text = "Country▼"
        End If

        lblHidSortType.Text = " ORDER BY Country " & SortType(lnkCountry.Text) & ", UnitFormat " & SortType(lnkUnitNumber.Text) & _
                            ", Project " & SortType(lnkProject.Text) & ", Road " & SortType(lnkRoad.Text) & _
                            ", State " & SortType(lnkState.Text) & ", Location " & SortType(lnkLocation.Text)
        LoadUnitNumber()
    End Sub

    'Added by mingin
    Protected Sub btnSaveAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSaveAdd.Click
        If lblUnitNumberUID.Text = "" Then
            'New
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

            'TODO : ProjectId Foreign Key Error
            objDB.OpenConnection()
            If UnitNumberExist(objDB) Then
                ClearAllField()
                divError.Visible = True
                lblMsgError.Text = "Location Already Exist"
                LoadUnitNumber()
            Else
                objDB.dbVariable.SQLString = "INSERT INTO AW_Unit (UnitId,CountryId,StateId,LocationId,RoadId,ProjectId,UnitFormat,CreateDate,StatusDelete) VALUES (@UnitId,@CountryId,@StateId,@LocationId,@RoadId,@ProjectId,@UnitFormat,@CreateDate,@StatusDelete)"
                objDB.AddParameter("UnitId", Data.SqlDbType.UniqueIdentifier, Guid.NewGuid)
                objDB.AddParameter("CountryId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlCountry.SelectedValue))
                objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlState.SelectedValue))
                objDB.AddParameter("LocationId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlLocation.SelectedValue))
                objDB.AddParameter("RoadId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlRoad.SelectedValue))
                If ddlProject.SelectedIndex = 0 Then
                    objDB.AddParameter("ProjectId", Data.SqlDbType.UniqueIdentifier, DBNull.Value)
                Else
                    objDB.AddParameter("ProjectId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlProject.SelectedValue))
                End If
                objDB.AddParameter("UnitFormat", Data.SqlDbType.NVarChar, txtUnitNumber.Text.Trim.ToUpper)
                objDB.AddParameter("CreateDate", Data.SqlDbType.DateTime, Now)
                objDB.AddParameter("StatusDelete", Data.SqlDbType.TinyInt, 0)
                objDB.ExecuteQuery()
                'ClearAllField()
                'divSuccessful.Visible = True
                'lblMsgSuccessful.Text = "Unit Number Format Successfully Inserted"
                'LoadUnitNumber()
                txtUnitNumber.Text = String.Empty
                lblUnitNumberUID.Text = ""
                LoadUnitNumber()
                mpeEntry.Show()
            End If
            objDB.CloseConnection()
        Else
            'Edit
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

            objDB.OpenConnection()
            objDB.dbVariable.SQLString = "UPDATE AW_Unit SET UnitFormat=@UnitFormat WHERE UnitId=@UnitId"
            objDB.AddParameter("UnitId", Data.SqlDbType.UniqueIdentifier, New Guid(lblUnitNumberUID.Text))
            objDB.AddParameter("UnitFormat", Data.SqlDbType.NVarChar, txtUnitNumber.Text.Trim.ToUpper)
            objDB.ExecuteQuery()
            objDB.CloseConnection()
            'ClearAllField()
            'divSuccessful.Visible = True
            'lblMsgSuccessful.Text = "Unit Number Format Successfully Updated"
            'LoadUnitNumber()
            txtUnitNumber.Text = String.Empty
            lblUnitNumberUID.Text = ""
            LoadUnitNumber()
            mpeEntry.Show()
        End If

    End Sub

    Protected Sub repUnitNumber_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles repUnitNumber.ItemDataBound
        If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then
            CType(e.Item.FindControl("cbeDelete"), Object).confirmText = "Are you sure you want to delete " & CType(e.Item.FindControl("lblRepUnitFormat"), Label).Text
        End If
    End Sub
End Class
